﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Data.SqlClient;
namespace Common
{/*
 Vesion: 适用于 NPOI 1.25版本
 Author: Irving
 Description: Execl操作
 Date:2012年6月25日17:11:39
 UpdateDate:2012年9月26日13:03:31 修正日期与导入数据库中的性能
 * 
 * 
 * 
 *          //必须与数据源中的列一致
            Dictionary<string, string> dtDictText = new Dictionary<string, string>();
            dtDictText.Add("ProductID", "唯一号");
            dtDictText.Add("ProductNo", "编号");
            dtDictText.Add("ProductName", "姓名");
            dtDictText.Add("Price", "价格");
            dtDictText.Add("Memo", "备注");

            NPOIsHelper.ExportByWeb(BLL.ProductBLL.LoadDataByDataSet(), dtDictText, "测试啦啦啦", HttpContext.Current);
            NPOIsHelper.ExportByWin(BLL.ProductBLL.LoadDataByDataSet(), dtDictText, Server.MapPath("/Files/" + "测试啦啦啦 (5).xls"));
            NPOIsHelper.Import(Server.MapPath("/Files/" + "测试啦啦啦 (2).xls"), 1);
            NPOIHSSFHelper.ImportExcelDBSourceIntoTable(DBSetting.ConText, "User_Action_Mapping", dtDictText, Server.MapPath("/Files/" + "987月北京会员入住记录.xls"));
 * 
 * 
 * 
 */
    public class NPOIHSSFHelper
    {
        #region public

        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="headerTextDic">头文本</param>
        /// <param name="fileName">文件路径</param>
        public static void ExportByWin(DataTable dtSource, Dictionary<string, string> headerTextDic, string filePath)
        {
            using (MemoryStream ms = Export(dtSource, headerTextDic, filePath, false))
            {
                using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        /// <summary>
        /// 用于Web导出
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="headerTextDic">头文本</param>
        /// <param name="fileName">文件名</param>
        public static void ExportByWeb(DataTable dtSource, Dictionary<string, string> headerTextDic, string fileName, HttpContext context)
        {
            // 设置编码和附件格式
            context.Response.ContentType = "application/vnd.ms-excel";
            context.Response.ContentEncoding = Encoding.UTF8;
            context.Response.Charset = "";
            context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName + ".xls", Encoding.UTF8));
            context.Response.BinaryWrite(Export(dtSource, headerTextDic, fileName, false).GetBuffer());
            context.Response.End();
        }

        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetNo">Sheet编号(默认为0,即sheet1,可选参数)</param>
        /// <returns>返回DataTable</returns>
        public static DataTable Import(string filePath, params int[] sheetNo)
        {
            int sheetNO;
            if (sheetNo.Length != 0)
                sheetNO = sheetNo[0];
            else
                sheetNO = 0;
            DataTable dt = new DataTable();
            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            HSSFSheet sheet = hssfworkbook.GetSheetAt(sheetNO) as HSSFSheet;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            HSSFRow headerRow = sheet.GetRow(0) as HSSFRow;
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                HSSFCell cell = headerRow.GetCell(j) as HSSFCell;
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                HSSFRow row = sheet.GetRow(i) as HSSFRow;
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        if (row.GetCell(j).CellType.ToString() != "NUMERIC")//处理时间格式化问题
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                        else
                        {
                            dataRow[j] = row.GetCell(j).DateCellValue.Date.ToString();
                            //HSSFCellStyle dateStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
                            //HSSFDataFormat format = hssfworkbook.CreateDataFormat() as HSSFDataFormat;
                            //dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");

                            //HSSFCell newCell = row.GetCell(j) as HSSFCell;
                            //newCell.CellStyle = dateStyle;
                            //dataRow[j] = row.GetCell(j).ToString();
                        }
                    }
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetNo">Sheet编号(默认为0,即sheet1,可选参数)</param>
        /// <returns>返回DataTable</returns>
        public static DataTable Import(FileStream file, params int[] sheetNo)
        {
            int sheetNO;
            if (sheetNo.Length != 0)
                sheetNO = sheetNo[0];
            else
                sheetNO = 0;
            DataTable dt = new DataTable();
            HSSFWorkbook hssfworkbook;
            using (file)
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            HSSFSheet sheet = hssfworkbook.GetSheetAt(sheetNO) as HSSFSheet;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            HSSFRow headerRow = sheet.GetRow(0) as HSSFRow;
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                HSSFCell cell = headerRow.GetCell(j) as HSSFCell;
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                HSSFRow row = sheet.GetRow(i) as HSSFRow;
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        if (row.GetCell(j).CellType.ToString() != "NUMERIC")//处理时间格式化问题
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                        else if (row.GetCell(j).CellType.ToString() == "NUMERIC")
                        {
                            if (row.GetCell(j).ToString().IndexOf("/") >= 0)
                            {
                                dataRow[j] = row.GetCell(j).DateCellValue.Date.ToString();
                            }
                            else
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }
                        else
                        {
                            dataRow[j] = row.GetCell(j).DateCellValue.Date.ToString();
                            //HSSFCellStyle dateStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
                            //HSSFDataFormat format = hssfworkbook.CreateDataFormat() as HSSFDataFormat;
                            //dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");

                            //HSSFCell newCell = row.GetCell(j) as HSSFCell;
                            //newCell.CellStyle = dateStyle;
                            //dataRow[j] = row.GetCell(j).ToString();
                        }
                    }
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }
        /// <summary>
        /// 读取Execl数据到数据库中
        /// </summary>
        /// <param name="conText">连接字符串</param>
        /// <param name="tabeName">表名</param>
        /// <param name="ipDict">字典(SourceColumn(数据源列名称)  DestinationColumn(目标列名称))</param>
        /// <param name="filePath">路径(只支持xls格式)</param>
        /// <param name="sheetNo">纸张页</param>
        public static void ImportExcelDBSourceIntoTable(string conText, string tabeName, Dictionary<string, string> ipDict, string filePath, params int[] sheetNo)
        {
            int sheetNO;
            if (sheetNo.Length != 0)
                sheetNO = sheetNo[0];
            else
                sheetNO = 0;
            using (SqlConnection conn = new SqlConnection(conText))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                DataTable dt = NPOIHSSFHelper.Import(filePath, sheetNO);
                SqlBulkCopy blkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran) { BatchSize = dt.Rows.Count, DestinationTableName = tabeName };
                foreach (KeyValuePair<string, string> item in ipDict)
                {
                    blkCopy.ColumnMappings.Add(item.Key, item.Value).ToString().Trim();
                }
                try
                {
                    blkCopy.WriteToServer(dt);
                    tran.Commit();
                    conn.Close();
                    blkCopy.Close();
                }
                catch (Exception ex)
                {   //Log4
                    tran.Rollback();
                    conn.Close();
                    blkCopy.Close();
                    throw new Exception(ex.Message);
                }
            }
        }
        #endregion

        #region private

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="headerTextDic">头文本</param>
        /// <param name="fileName">文件名</param>
        /// <param name="isShowTitle">是否显示表头</param>
        /// <returns></returns>
        private static MemoryStream Export(DataTable dtSource, Dictionary<string, string> headerTextDic, string fileName, bool isShowTitle)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
            SetSummaryInformation(workbook);
            HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
            int[] arrColWidth = GetColWidth(dtSource, headerTextDic);
            int rowIndex = 0;
            HSSFCellStyle colHeaderStyle = GetColHeaderStyle(workbook);
            foreach (DataRow row in dtSource.Rows)
            {
                if (rowIndex == 65536 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet() as HSSFSheet;
                    }
                    SetDataTitle(headerTextDic, fileName, isShowTitle, workbook, sheet);
                    SetColHeader(dtSource, headerTextDic, isShowTitle, workbook, sheet, arrColWidth, colHeaderStyle);
                    if (isShowTitle)
                    {
                        rowIndex = 2;
                    }
                    else
                    {
                        rowIndex = 1;
                    }
                }
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                SetContentData(dtSource, headerTextDic, dateStyle, row, dataRow, workbook);
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        /// <summary>
        /// 右击文件/属性信息
        /// </summary>
        /// <param name="workbook"></param>
        private static void SetSummaryInformation(HSSFWorkbook workbook)
        {
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "Irving's Company";
            workbook.DocumentSummaryInformation = dsi;
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "Irving"; //填加xls文件作者信息
            si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
            si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
            si.Comments = "作者信息"; //填加xls文件作者信息
            si.Title = "标题信息"; //填加xls文件标题信息
            si.Subject = "主题信息";//填加文件主题信息
            si.CreateDateTime = DateTime.Now;
            workbook.SummaryInformation = si;
        }

        /// <summary>
        /// 取得列宽
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="headerTextDic"></param>
        /// <returns></returns>
        private static int[] GetColWidth(DataTable dtSource, Dictionary<string, string> headerTextDic)
        {
            int[] arrColWidth = new int[headerTextDic.Count];
            int index = 0;
            foreach (KeyValuePair<string, string> item in headerTextDic)
            {
                arrColWidth[index] = Encoding.GetEncoding(936).GetBytes(item.Value).Length;
                index++;
            }
            index = 0;
            foreach (KeyValuePair<string, string> item in headerTextDic)
            {
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        if (item.Key.ToLower() == dtSource.Columns[j].ColumnName.ToLower())
                        {
                            int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                            if (intTemp > arrColWidth[index])
                            {
                                arrColWidth[index] = intTemp;
                            }
                            break;
                        }
                    }
                }
                index++;
            }

            return arrColWidth;
        }

        /// <summary>
        /// 表头及样式
        /// </summary>
        /// <param name="headerTextDic"></param>
        /// <param name="fileName"></param>
        /// <param name="isShowTitle"></param>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        private static void SetDataTitle(Dictionary<string, string> headerTextDic, string fileName, bool isShowTitle, HSSFWorkbook workbook, HSSFSheet sheet)
        {
            if (isShowTitle)
            {
                HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
                headerRow.HeightInPoints = 25;
                headerRow.CreateCell(0).SetCellValue(fileName);

                HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                headStyle.Alignment = HorizontalAlignment.CENTER;
                HSSFFont font = workbook.CreateFont() as HSSFFont;
                font.FontHeightInPoints = 20;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                headerRow.GetCell(0).CellStyle = headStyle;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, headerTextDic.Count - 1));
                // sheet.AddMergedRegion(new Region(0, 0, 0, headerTextDic.Count - 1));已过时
            }
        }

        /// <summary>
        /// 列头数据
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="headerTextDic"></param>
        /// <param name="isShowTitle"></param>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="arrColWidth"></param>
        private static void SetColHeader(DataTable dtSource, Dictionary<string, string> headerTextDic, bool isShowTitle, HSSFWorkbook workbook, HSSFSheet sheet, int[] arrColWidth, HSSFCellStyle colHeaderStyle)
        {
            HSSFRow headerRow = null;
            if (isShowTitle)
            {
                headerRow = sheet.CreateRow(1) as HSSFRow;
            }
            else
            {
                headerRow = sheet.CreateRow(0) as HSSFRow;
            }

            int index = 0;

            foreach (KeyValuePair<string, string> item in headerTextDic)
            {
                foreach (DataColumn column in dtSource.Columns)
                {
                    if (column.ColumnName.ToLower() == item.Key.ToLower())
                    {
                        headerRow.CreateCell(index).SetCellValue(item.Value);
                        headerRow.GetCell(index).CellStyle = colHeaderStyle;

                        int colWidth = arrColWidth[index];
                        if (colWidth > 254)
                        {
                            colWidth = 254;
                        }

                        sheet.SetColumnWidth(index, (colWidth + 1) * 256);
                        index++;
                        break;
                    }
                }
            }
        }

        /// <summary>
        /// 填充内容
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="headerTextDic"></param>
        /// <param name="dateStyle"></param>
        /// <param name="row"></param>
        /// <param name="dataRow"></param>
        private static void SetContentData(DataTable dtSource, Dictionary<string, string> headerTextDic, HSSFCellStyle dateStyle, DataRow row, HSSFRow dataRow, HSSFWorkbook workbook)
        {
            int index = 0;
            foreach (KeyValuePair<string, string> item in headerTextDic)
            {
                foreach (DataColumn column in dtSource.Columns)
                {
                    if (item.Key.ToLower() == column.ColumnName.ToLower())
                    {
                        HSSFCell newCell = dataRow.CreateCell(index) as HSSFCell;
                        string drValue = row[column].ToString();

                        switch (column.DataType.ToString())
                        {
                            case "System.String"://字符串类型
                                newCell.SetCellValue(drValue);
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                if (drValue.Contains("上午 12:00:00"))
                                {
                                    drValue = dateV.ToString("yyyy/MM/dd");
                                }
                                else
                                {
                                    if (!string.IsNullOrEmpty(drValue))
                                    {
                                        drValue = dateV.ToString();
                                    }
                                }
                                newCell.SetCellValue(drValue);

                                newCell.CellStyle = dateStyle;//格式化显示
                                break;
                            case "System.Boolean"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            case "System.Guid"://GUID
                                Guid guid = Guid.Empty;
                                Guid.TryParse(drValue, out guid);
                                newCell.SetCellValue(guid.ToString());
                                break;
                            case "System.DBNull"://空值处理
                                newCell.SetCellValue("");
                                break;
                            default:
                                newCell.SetCellValue("");
                                break;
                        }
                        index++;
                        break;
                    }
                }
            }
        }

        #region 樣式

        /// <summary>
        /// 列头樣式
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private static HSSFCellStyle GetColHeaderStyle(HSSFWorkbook workbook)
        {
            HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            headStyle.Alignment = HorizontalAlignment.CENTER;
            HSSFFont font = workbook.CreateFont() as HSSFFont;
            font.FontHeightInPoints = 10;
            font.Boldweight = 700;
            headStyle.SetFont(font);


            HSSFPalette palette = workbook.GetCustomPalette();
            palette.SetColorAtIndex((short)10, (byte)0, (byte)176, (byte)240);
            headStyle.FillForegroundColor = (short)10;
            headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            return headStyle;

        }


        #endregion

        #endregion
    }
}
